import sqlite3
import os
import datetime
import shutil
import pymysql
# 使用统一的日志配置
from logging_config import get_logger

# 获取模块logger
logger = get_logger('utils')

# 全局变量，将在main.py中初始化
CONFIG = None
ROOTPATH = None
mysql_pool = None

# 初始化函数，由main.py调用
def initialize(config_data, pool=None):
    """初始化utils模块所需的配置"""
    global CONFIG, ROOTPATH, mysql_pool
    CONFIG = config_data
    ROOTPATH = os.path.abspath(CONFIG["root_dir"].replace('\\', '/'))
    mysql_pool = pool

# 常量定义
LOCAL_DB_NAME = "local.db"
RECYBIN_PATH = "recyclebin"
KB = 1024

def get_mysql_conn(max_retries=2):
    """获取MySQL连接 - 使用DBUtils连接池，增加重试机制"""
    # 直接从DBUtils连接池获取连接
    if not mysql_pool:
        logger.error("MySQL connection pool not initialized")
        return None
    
    retry_count = 0
    while retry_count <= max_retries:
        try:
            conn = mysql_pool.connection()
            # DBUtils的SteadyDBConnection没有autocommit属性
            return conn
        except Exception as e:
            retry_count += 1
            logger.error(f"Failed to get connection from pool (attempt {retry_count}/{max_retries}): {e}")
            
            # 如果是最后一次重试失败
            if retry_count > max_retries:
                logger.critical(f"All connection attempts failed. Connection pool status check recommended.")
                return None
                
            # 短暂等待后重试
            import time
            time.sleep(0.1)  # 100ms后重试
    
    return None

# 创建文件夹以及SQLite数据库
def create_folder_sql(corpid: str, path: str = ""):  
    """创建文件夹和SQLite数据库，支持跨平台路径处理"""
    # 构建路径时统一使用os.path.join确保跨平台兼容性
    if path:
        # 移除开头的/，使用os.path.join处理路径连接
        path_parts = path.lstrip('/').split('/')
        db_dir = ROOTPATH
        for part in [corpid] + path_parts:
            db_dir = os.path.join(db_dir, part)
    else:
        # 使用corpid根目录
        db_dir = os.path.join(ROOTPATH, corpid)  
    
    try:
        os.makedirs(db_dir, exist_ok=True)
        logger.debug(f"Created directory: {db_dir}")
    except Exception as e:
        logger.error(f"Failed to create directory {db_dir}: {e}")
        return 1    
    
    db_path = os.path.join(db_dir, LOCAL_DB_NAME)    
    try:
        conn = sqlite3.connect(db_path)
        
        if os.path.getsize(db_path) == 0:
            create_tables(conn)
        conn.close()
        logger.debug(f"SQLite database initialized at: {db_path}")
        return 0
    except Exception as e:
        logger.error(f"SQLite connection failed for {db_path}: {e}")
        return 2

# 连接SQLite数据库
def nas_sqlite_dirdb_get(corpid: str, path: str = ""):   
    """获取SQLite数据库连接，支持跨平台路径处理"""
    # 构建路径时统一使用os.path.join确保跨平台兼容性
    if path:
        path_parts = path.lstrip('/').split('/')
        db_dir = ROOTPATH
        for part in [corpid] + path_parts:
            db_dir = os.path.join(db_dir, part)
    else:
        db_dir = os.path.join(ROOTPATH, corpid)    
    
    db_path = os.path.join(db_dir, LOCAL_DB_NAME)
    
    if not os.path.exists(db_path):
        logger.warning(f"Database file does not exist: {db_path}")
        return None
    
    try:
        conn = sqlite3.connect(db_path)
        # 检查数据库文件是否为新创建的（大小为0），只有在新创建时才执行表结构创建
        if os.path.getsize(db_path) == 0:
            create_tables(conn)
        return conn
    except Exception as e:
        print(f"SQLite连接失败: {e}")
        return None

# 向SQLite数据库添加文件记录
def nas_add_file(corpid: str, fpath: str,fid: str,name: str,  sql: str):   
    # 检查fid和name是否存在
    if not fid or not name:
        print(f"fid或name不存在: fid={fid}, name={name}")
        return -4
    # 获取数据库连接
    conn = nas_sqlite_dirdb_get(corpid, fpath)
    if not conn:
        print(f"无法获取数据库连接: corpid={corpid}, fpath={fpath}")
        return -1    
    try:
        cursor = conn.cursor()       
        cursor.execute("SELECT COUNT(*) FROM file_info WHERE fid = ? OR name = ?", (fid, name))
        count = cursor.fetchone()[0]
        if count > 0:
            print(f"文件记录已存在: fid={fid}, name={name}")
            return -2        
        cursor.execute(sql)
        conn.commit()
        return 0
    except Exception as e:
        print(f"执行SQL插入失败: {e}")
        conn.rollback()
        return -3
    finally:
        if conn:
            try:
                conn.close()
            except:
                pass

# 删除SQLite数据库
def nas_sqlite_dirdb_del(db_path):
    """删除SQLite数据库文件"""
    try:
        if os.path.exists(db_path):
            os.remove(db_path)
            return 0
    except Exception as e:
        print(f"删除数据库文件失败: {e}")
    return -1

# 创建SQLite表
def create_tables(conn):
    """
    创建SQLite表结构
    
    Args:
        conn: SQLite连接对象
    """
    cursor = conn.cursor()
    # 创建文件表 - 根据C代码中的逻辑，表结构需要包含name, nodeid, pid, uid, filetype, tid, ctime等字段 vpath 恢复路径  filepath nodeid:pathid
    # pid 0 公共 1 私有  
    cursor.execute('''
    CREATE TABLE IF NOT EXISTS file_info (
        seqno INTEGER PRIMARY KEY AUTOINCREMENT,
        stamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
        fid TEXT DEFAULT '',
        name TEXT NOT NULL,
        ctime TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
        mtime TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
        cursize INTEGER NOT NULL DEFAULT 0,
        fsize INTEGER NOT NULL DEFAULT 0,
        filetype INTEGER NOT NULL DEFAULT 0,
        uid INTEGER NOT NULL DEFAULT 0,
        tid INTEGER NOT NULL DEFAULT 0,
        nodeid INTEGER NOT NULL DEFAULT 0,
        pid INTEGER NOT NULL DEFAULT 0,
        filepath TEXT NOT NULL DEFAULT '',
        vpath TEXT NOT NULL DEFAULT '',
        passwd TEXT NOT NULL DEFAULT '',
        srctid TEXT NOT NULL DEFAULT '',
        auxfid TEXT NOT NULL DEFAULT '',
        delflag INTEGER NOT NULL DEFAULT 0,
        fsource TEXT NOT NULL DEFAULT '',
        mimeid INTEGER DEFAULT 0
    )
    ''')
    # 创建索引 - 添加pid索引以优化查询
    cursor.execute('CREATE INDEX IF NOT EXISTS idx_name ON file_info (name)')
    cursor.execute('CREATE INDEX IF NOT EXISTS idx_pid ON file_info (pid)')
    conn.commit()

# 创建企业目录结构
def nas_create_corp_v1(corpid: str, uid: str):
    """
    创建企业目录结构，对应C语言版本的同名函数
    
    Args:
        corpid: 企业ID
        uid: 用户ID
    
    Returns:
        int: 0表示成功，非0表示失败
    """   
    result = create_folder_sql(corpid)
    if result != 0:
        print(f"创建企业根目录数据库失败: 错误码 {result}")
        return -1    
    # 创建默认目录   
    public_dir = "公共空间"  # 公共目录
    recycle_root="回收站"
    username = "私有空间"+uid   
    nas_add_folder_create_v1(corpid, "/", recycle_root, 0, uid, 0)   
    nas_add_folder_create_v1(corpid, "/", public_dir, 0, uid, 0)
    # 根据uid查询mysql获取到nickname作为目录名称
    result = query_mysql("SELECT nickname FROM employees WHERE corpid = %s AND uid = %s limit 1", (corpid, uid))
    if result:
        username = result[0]
    nas_add_folder_create_v1(corpid, "/", username, 1, uid, 0)
    return 0

# 通用MySQL查询函数
def query_mysql(sql_query, params=None, fetchone=True):
    """
    执行MySQL查询的通用函数（仅用于SELECT操作）
    
    Args:
        sql_query: SQL查询语句
        params: 查询参数，默认为None
        fetchone: 是否只返回一条记录，默认为True
    
    Returns:
        fetchone=True时返回单条记录（字典）或None，fetchone=False时返回记录列表或None
    """
    conn = get_mysql_conn()
    if not conn:
        logger.error("获取MySQL连接失败")
        return None
    
    cursor = None
    try:
        # 创建游标，使用PyMySQL的DictCursor返回字典格式结果
        cursor = conn.cursor(pymysql.cursors.DictCursor)
        
        # 执行查询
        cursor.execute(sql_query, params or ())
        
        # 根据fetchone参数决定返回单行还是多行
        if fetchone:
            result = cursor.fetchone()
        else:
            result = cursor.fetchall()
            
        return result
    except Exception as e:
        logger.error(f"MySQL查询失败: {e}")
        return None
    finally:
        # 确保资源正确释放
        if cursor:
            try:
                cursor.close()
            except Exception as e:
                logger.warning(f"关闭游标失败: {e}")
        
        # 关闭连接（DBUtils会自动处理连接的返回）
        try:
            conn.close()
        except Exception as e:
            logger.warning(f"关闭连接失败: {e}")


# 通用MySQL修改操作函数（仅用于UPDATE、INSERT、DELETE操作）
def exec_mysql(sql_query, params=None):
    """
    执行MySQL修改操作的通用函数（用于UPDATE、INSERT、DELETE操作）
    
    Args:
        sql_query: SQL语句
        params: SQL参数，默认为None
    
    Returns:
        成功返回True，失败返回False
    """
    conn = get_mysql_conn()
    if not conn:
        logger.error("获取MySQL连接失败")
        return False
    
    cursor = None
    try:
        # 直接创建游标
        cursor = conn.cursor()
        
        # 执行SQL
        cursor.execute(sql_query, params or ())
        
        # 提交事务
        conn.commit()
        return True
    except Exception as e:
        # 回滚事务
        try:
            if conn:
                conn.rollback()
        except Exception as rollback_error:
            logger.warning(f"事务回滚失败: {rollback_error}")
        
        logger.error(f"MySQL修改操作失败: {e}")
        return False
    finally:
        # 确保资源正确释放
        if cursor:
            try:
                cursor.close()
            except Exception as e:
                logger.warning(f"关闭游标失败: {e}")
        
        # 关闭连接（DBUtils会自动处理连接的返回）
        try:
            conn.close()
        except Exception as e:
            logger.warning(f"关闭连接失败: {e}")

# 批量执行MySQL修改操作（多条SQL，同一事务）
def execute_mysql_batch(sql_commands, params_list=None):
    """
    批量执行MySQL修改操作，所有操作在同一事务中
    
    Args:
        sql_commands: SQL语句列表
        params_list: 参数列表，每个元素对应一条SQL的参数
    
    Returns:
        成功返回True，失败返回False
    """
    conn = None
    cursor = None
    
    try:
        # 获取数据库连接
        conn = get_mysql_conn()
        if not conn:
            logger.error("获取MySQL连接失败")
            return False
        
        # 创建游标
        cursor = conn.cursor()
        
        # 开启事务
        conn.begin()
        
        # 执行所有SQL语句
        for i, sql in enumerate(sql_commands):
            params = params_list[i] if params_list and i < len(params_list) else ()
            cursor.execute(sql, params)
        
        # 提交事务
        conn.commit()
        return True
        
    except Exception as e:
        logger.error(f"批量MySQL操作失败: {e}")
        # 回滚事务
        if conn:
            try:
                conn.rollback()
            except Exception as rollback_error:
                logger.warning(f"事务回滚失败: {rollback_error}")
        return False
        
    finally:
        # 关闭游标和连接
        if cursor:
            try:
                cursor.close()
            except Exception as e:
                logger.warning(f"关闭游标失败: {e}")
        if conn:
            try:
                conn.close()
            except Exception as e:
                logger.warning(f"关闭连接失败: {e}")


# 创建NAS文件夹 上层目录增加记录
def nas_add_folder_create_v1(corpid: str, fpath: str, name: str, pid: int, uid: int, filetype: int):
    """ 
    Args:
        corpid: 企业ID
        fpath: 上级目录路径
        name: 目录名称
        pid: 权限ID，0表示公共空间，1表示私有目录，2表示input/output，其他为groupid
        uid: 用户ID
        filetype: 文件类型，0表示文件夹，6表示特殊文件夹    
    Returns:
        int: 0表示成功， -1父目录不存在 -2目录创建失败  -3表示目录已存
    """
    # 构建目录路径
    fdir = os.path.join(ROOTPATH, str(corpid), fpath.lstrip('/'), name)
    fppath = os.path.join(ROOTPATH, str(corpid), fpath.lstrip('/'))    
    # 检查父目录是否存在
    if not os.path.exists(fppath):
        print(f"父目录不存在: {fppath}")
        return -1 
    # 检查目录是否已存在
    if os.path.exists(fdir):
        print(f"目录已存在: {fdir}")
        return -3      
    # 创建目录以及sql    
    ret = create_folder_sql(corpid,fdir)
    if ret != 0:
        return -2     
    # 在父目录数据库中插入一条记录
    pdirdb_path = os.path.join(fppath, LOCAL_DB_NAME)
    pdirdb = nas_sqlite_dirdb_get(str(corpid), fpath) 
    if pdirdb:
        try:
            cursor = pdirdb.cursor()
            current_time = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
            # 按照name, nodeid, pid, uid, filetype, tid, ctime的顺序准备数据
            data = (name, 1, pid, uid, filetype, 0, current_time)
            cursor.execute(
                "INSERT INTO file_info (name, nodeid, pid, uid, filetype, tid, ctime) VALUES (?, ?, ?, ?, ?, ?, ?)",
                data
            )
            pdirdb.commit()
        except Exception as e:
            print(f"在父目录数据库中插入记录失败: {e}")
            pdirdb.rollback()
        finally:
            pdirdb.close()    
    return 0

# 重命名NAS文件夹并更新上级目录中的SQLite记录
def nas_rename_folder(corpid: str, fpath: str, name: str, newname: str, pid: int, uid: int):  
    old_folder_path = os.path.join(ROOTPATH, str(corpid), fpath.lstrip('/'), name)
    new_folder_path = os.path.join(ROOTPATH, str(corpid), fpath.lstrip('/'), newname)
    parent_dir_path = os.path.join(ROOTPATH, str(corpid), fpath.lstrip('/'))
    
    # 检查父目录是否存在
    if not os.path.exists(parent_dir_path):
        print(f"父目录不存在: {parent_dir_path}")
        return -1
    
    # 检查原文件夹是否存在
    if not os.path.exists(old_folder_path):
        print(f"原文件夹不存在: {old_folder_path}")
        return -2    
    # 检查新文件夹是否已存在
    if os.path.exists(new_folder_path):
        print(f"新文件夹名称已存在: {new_folder_path}")
        return -3    
    try:
        # 使用操作系统函数重命名文件夹
        os.rename(old_folder_path, new_folder_path)
    except Exception as e:
        print(f"重命名文件夹失败: {e}")
        return -3
    
    # 获取父目录的SQLite数据库连接
    parent_db = nas_sqlite_dirdb_get(str(corpid), fpath)
    if not parent_db:
        print("获取父目录SQLite数据库连接失败")
        return -4    
    try:
        cursor = parent_db.cursor()
        # 更新父目录中对应的文件夹名称记录
        cursor.execute(
            "UPDATE file_info SET name = ? WHERE name = ? AND uid = ? AND filetype = 0",
            (newname, name,uid)
        )
        parent_db.commit()        
        # 检查更新是否成功
        if cursor.rowcount == 0:
            print(f"未找到需要更新的记录: name={name}, pid={pid}, uid={uid}")
            return -4        
        return 0
    except Exception as e:
        print(f"更新SQLite记录失败: {e}")        
        return -4
    finally:
        if parent_db:
            try:
                parent_db.close()
            except:
                pass

# 移动NAS文件夹并更新SQLite记录
def nas_move_folder(corpid: str, src_fpath: str, src_name: str, dst_fpath: str, dst_name: str, uid: int):  
    """
    移动文件夹并更新SQLite记录
    
    Args:
        corpid: 企业ID
        src_fpath: 源文件夹的上级目录路径
        src_name: 源文件夹名称
        dst_fpath: 目标文件夹的上级目录路径
        dst_name: 目标文件夹名称
        uid: 用户ID
    
    Returns:
        int: 0表示成功，-1源父目录不存在，-2目标父目录不存在，-3源文件夹不存在，
             -4目标文件夹已存在，-5移动失败，-6更新源目录记录失败，-7更新目标目录记录失败
    """
    # 构建路径
    src_folder_path = os.path.join(ROOTPATH, str(corpid), src_fpath.lstrip('/'), src_name)
    dst_folder_path = os.path.join(ROOTPATH, str(corpid), dst_fpath.lstrip('/'), dst_name)
    src_parent_path = os.path.join(ROOTPATH, str(corpid), src_fpath.lstrip('/'))
    dst_parent_path = os.path.join(ROOTPATH, str(corpid), dst_fpath.lstrip('/'))
    
    # 检查源父目录是否存在
    if not os.path.exists(src_parent_path):
        print(f"源父目录不存在: {src_parent_path}")
        return -1
    
    # 检查目标父目录是否存在
    if not os.path.exists(dst_parent_path):
        print(f"目标父目录不存在: {dst_parent_path}")
        return -2
    
    # 检查源文件夹是否存在
    if not os.path.exists(src_folder_path):
        print(f"源文件夹不存在: {src_folder_path}")
        return -3
    
    # 检查目标文件夹是否已存在
    if os.path.exists(dst_folder_path):
        print(f"目标文件夹已存在: {dst_folder_path}")
        return -4
    
    # 从源父目录数据库获取文件夹信息
    src_parent_db = nas_sqlite_dirdb_get(str(corpid), src_fpath)
    if not src_parent_db:
        print("获取源父目录SQLite数据库连接失败")
        return -6
    
    file_info = None
    try:
        cursor = src_parent_db.cursor()
        cursor.execute(
            "SELECT name, pid, uid, filetype, tid, ctime FROM file_info WHERE name = ? AND uid = ? AND filetype = 0",
            (src_name, uid)
        )
        file_info = cursor.fetchone()
        if not file_info:
            print(f"未找到源文件夹的记录: {src_name}")
            return -6
    except Exception as e:
        print(f"查询源文件夹信息失败: {e}")
        return -6
    finally:
        if src_parent_db:
            try:
                src_parent_db.close()
            except:
                pass
    
    try:
        # 使用操作系统函数移动文件夹
        os.rename(src_folder_path, dst_folder_path)
    except Exception as e:
        print(f"移动文件夹失败: {e}")
        return -5
    
    # 删除源父目录中的记录
    src_parent_db = nas_sqlite_dirdb_get(str(corpid), src_fpath)
    if not src_parent_db:
        print("获取源父目录SQLite数据库连接失败")
        return -6
    
    try:
        cursor = src_parent_db.cursor()
        cursor.execute(
            "DELETE FROM file_info WHERE name = ? AND uid = ? AND filetype = 0",
            (src_name, uid)
        )
        src_parent_db.commit()
        if cursor.rowcount == 0:
            print(f"删除源文件夹记录失败")
            return -6
    except Exception as e:
        print(f"删除源文件夹记录失败: {e}")
        return -6
    finally:
        if src_parent_db:
            try:
                src_parent_db.close()
            except:
                pass
    
    # 在目标父目录中添加记录
    dst_parent_db = nas_sqlite_dirdb_get(str(corpid), dst_fpath)
    if not dst_parent_db:
        print("获取目标父目录SQLite数据库连接失败")
        return -7
    
    try:
        cursor = dst_parent_db.cursor()
        # 使用原有的信息，但更新名称
        data = (dst_name, file_info[1], file_info[2], file_info[3], file_info[4], file_info[5])
        cursor.execute(
            "INSERT INTO file_info (name, pid, uid, filetype, tid, ctime) VALUES (?, ?, ?, ?, ?, ?)",
            data
        )
        dst_parent_db.commit()
    except Exception as e:
        print(f"在目标父目录中添加记录失败: {e}")
        return -7
    finally:
        if dst_parent_db:
            try:
                dst_parent_db.close()
            except:
                pass
    
    return 0

# 拷贝NAS文件夹并更新SQLite记录
def nas_copy_folder(corpid: str, src_fpath: str, src_name: str, dst_fpath: str, dst_name: str, uid: int):  
    """
    拷贝文件夹并更新SQLite记录
    
    Args:
        corpid: 企业ID
        src_fpath: 源文件夹的上级目录路径
        src_name: 源文件夹名称
        dst_fpath: 目标文件夹的上级目录路径
        dst_name: 目标文件夹名称
        uid: 用户ID
    
    Returns:
        int: 0表示成功，-1源父目录不存在，-2目标父目录不存在，-3源文件夹不存在，
             -4目标文件夹已存在，-5拷贝失败，-6获取源文件夹信息失败，-7更新目标目录记录失败
    """
    # 构建路径
    src_folder_path = os.path.join(ROOTPATH, str(corpid), src_fpath.lstrip('/'), src_name)
    dst_folder_path = os.path.join(ROOTPATH, str(corpid), dst_fpath.lstrip('/'), dst_name)
    src_parent_path = os.path.join(ROOTPATH, str(corpid), src_fpath.lstrip('/'))
    dst_parent_path = os.path.join(ROOTPATH, str(corpid), dst_fpath.lstrip('/'))
    
    # 检查源父目录是否存在
    if not os.path.exists(src_parent_path):
        print(f"源父目录不存在: {src_parent_path}")
        return -1
    
    # 检查目标父目录是否存在
    if not os.path.exists(dst_parent_path):
        print(f"目标父目录不存在: {dst_parent_path}")
        return -2
    
    # 检查源文件夹是否存在
    if not os.path.exists(src_folder_path):
        print(f"源文件夹不存在: {src_folder_path}")
        return -3
    
    # 检查目标文件夹是否已存在
    if os.path.exists(dst_folder_path):
        print(f"目标文件夹已存在: {dst_folder_path}")
        return -4
    
    # 从源父目录数据库获取文件夹信息
    src_parent_db = nas_sqlite_dirdb_get(str(corpid), src_fpath)
    if not src_parent_db:
        print("获取源父目录SQLite数据库连接失败")
        return -6
    
    file_info = None
    try:
        cursor = src_parent_db.cursor()
        cursor.execute(
            "SELECT name, pid, uid, filetype, tid, ctime FROM file_info WHERE name = ? AND uid = ? AND filetype = 0",
            (src_name, uid)
        )
        file_info = cursor.fetchone()
        if not file_info:
            print(f"未找到源文件夹的记录: {src_name}")
            return -6
    except Exception as e:
        print(f"查询源文件夹信息失败: {e}")
        return -6
    finally:
        if src_parent_db:
            try:
                src_parent_db.close()
            except:
                pass
    
    try:
        # 使用操作系统函数拷贝文件夹
        shutil.copytree(src_folder_path, dst_folder_path)
    except Exception as e:
        print(f"拷贝文件夹失败: {e}")
        return -5
    
    # 在目标父目录中添加记录
    dst_parent_db = nas_sqlite_dirdb_get(str(corpid), dst_fpath)
    if not dst_parent_db:
        print("获取目标父目录SQLite数据库连接失败")
        return -7
    
    try:
        cursor = dst_parent_db.cursor()
        # 使用原有的信息，但更新名称和创建时间
        current_time = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        data = (dst_name, file_info[1], file_info[2], file_info[3], file_info[4], current_time)
        cursor.execute(
            "INSERT INTO file_info (name, pid, uid, filetype, tid, ctime) VALUES (?, ?, ?, ?, ?, ?)",
            data
        )
        dst_parent_db.commit()
    except Exception as e:
        print(f"在目标父目录中添加记录失败: {e}")
        return -7
    finally:
        if dst_parent_db:
            try:
                dst_parent_db.close()
            except:
                pass
    
    return 0

def nas_delete(corpid: str, fpath: str, name: str, filetype: int, fid: str):    
    parent_db = None
    try:
        # 获取父目录数据库连接
        parent_db = nas_sqlite_dirdb_get(str(corpid), fpath)
        if not parent_db:
            print(f"获取父目录数据库失败: {fpath}")
            return -1
        
        # 根据filetype决定使用name还是fid作为查询条件
        cursor = parent_db.cursor()
        if filetype == 0:           
            sql = "UPDATE file_info SET delflag = 1 WHERE name = ?"
            params = (name,)
        else:
            sql = "UPDATE file_info SET delflag = 1 WHERE fid = ?"
            params = (fid,)
        # 执行更新
        cursor.execute(sql, params) 
        if cursor.rowcount == 0:
            print(f"未找到要删除的记录: name={name}, fid={fid}")
            return -2
        parent_db.commit()
        print(f"成功将记录标记为删除: name={name}, fid={fid}")
        return 0        
    except Exception as e:
        print(f"删除操作失败: {e}")       
        return -3        
    finally:
        # 关闭数据库连接
        if parent_db:
            try:
                parent_db.close()
            except:
                pass

# 查找最优的edgesrv - 根据在线状态和磁盘空间返回
def get_best_edgesrv():
    if not CONFIG["edgesrv_list"]:
        return None
    
    # 模拟获取每个edgesrv的在线状态和磁盘空间
    available_edgesrvs = []
    for edgesrv in CONFIG["edgesrv_list"]:
        # 模拟获取在线状态（假设都是在线的）
        is_online = True
        
        # 模拟获取磁盘空间（这里为了演示，假设ID为1的磁盘空间更大）
        if edgesrv["id"] == 1:
            disk_space = 1000000000  # 1GB
        else:
            disk_space = 500000000   # 500MB
        
        # 只添加在线的edgesrv
        if is_online:
            available_edgesrvs.append({**edgesrv, "disk_space": disk_space})
    
    # 如果没有在线的edgesrv，返回None
    if not available_edgesrvs:
        return None
    
    # 按照磁盘空间降序排序
    sorted_edgesrvs = sorted(available_edgesrvs, key=lambda x: x["disk_space"], reverse=True)
    
    # 返回最优的edgesrv
    return sorted_edgesrvs[0]

# 创建MySQL文件信息表 - 企业文件数据
def create_mysql_fileinfo_table(corpid):
    conn = get_mysql_conn()
    if not conn:
        logger.error("Failed to get MySQL connection for creating fileinfo table")
        return False
    
    try:
        cursor = conn.cursor()
        # 表名使用前缀+企业ID，避免多企业数据冲突
        table_name = f"file_info_{corpid}"
        cursor.execute(f'''
            CREATE TABLE IF NOT EXISTS {table_name} (                   
            `seqno` int(11) NOT NULL AUTO_INCREMENT,
            `stamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
            `fid` varchar(33) NOT NULL DEFAULT '',
            `name` varchar(1024) NOT NULL,
            `ctime` timestamp NOT NULL DEFAULT '2021-01-30 13:05:01',
            `mtime` timestamp NOT NULL DEFAULT '2021-01-30 13:05:01',
            `fsize` bigint(20) NOT NULL DEFAULT '0',
            `cursize` bigint(20) NOT NULL DEFAULT '0',
            `filetype` int(11) NOT NULL DEFAULT '0',
            `uid` bigint(11) NOT NULL DEFAULT '0',
            `tid` bigint(11) NOT NULL DEFAULT '0',
            `nodeid` bigint(20) NOT NULL DEFAULT '0' COMMENT 'file nodeid',
            `pid` bigint(20) NOT NULL DEFAULT '0',
            `fpath` varchar(1024) NOT NULL COMMENT 'mac:path',
            `vpath` varchar(1024) NOT NULL COMMENT 'mac:path',
            `passwd` varchar(32) NOT NULL DEFAULT '',
            `srctid` bigint(11) NOT NULL DEFAULT '0',
            `auxfid` varchar(33) NOT NULL DEFAULT '',
            `delflag` int(11) NOT NULL DEFAULT '0',
            `mimeid` int(11) DEFAULT NULL,
            `resolvepower` varchar(32) NOT NULL DEFAULT '',
            `codingformat` varchar(32) NOT NULL DEFAULT '',
            `coderate` varchar(32) NOT NULL DEFAULT '',
            `codetime` varchar(32) NOT NULL DEFAULT '',
            `framerate` varchar(32) NOT NULL DEFAULT '',
            `channels` varchar(32) NOT NULL DEFAULT '',
            `samplerate` varchar(32) NOT NULL DEFAULT '',
            `fsource` varchar(32) NOT NULL DEFAULT '',
            `metadesc` varchar(512) NOT NULL DEFAULT '',
            `inuse` tinyint(3) DEFAULT '1',
            PRIMARY KEY (`seqno`),
            KEY `fidindex` (`fid`)
            ) ENGINE=InnoDB AUTO_INCREMENT=849 DEFAULT CHARSET=utf8

        ''')
        conn.commit()
        logger.info(f"MySQL fileinfo table created successfully for corp: {corpid}")
        return True
    except Exception as e:
        logger.error(f"Failed to create MySQL fileinfo table for corp {corpid}: {e}")
        return False
    finally:
        if conn:
            conn.close()
